package com.example.mapp.buildSql;

import java.util.Map;

public class IndexEchartsMappSql {

    /**
     * 辖区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribType() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT et.id ,et.type_name AS 'name',COUNT(e.elevator_type_id) AS 'count' FROM elevator_type et ");
        sb.append("LEFT JOIN elevator e ON e.elevator_type_id = et.id GROUP BY et.id order by COUNT(e.elevator_type_id) desc  ");
        return sb.toString();
    }

    /**
     * 市辖区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribTypeCity(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select COUNT(e.elevator_type_id) AS 'count',et.type_name as 'name' from elevator e ");
        sb.append(" LEFT JOIN elevator_type et ON e.elevator_type_id = et.id ");
        sb.append(" where e.branch_office is not null and elevator_type_id is not null ");
        sb.append(" GROUP BY e.elevator_type_id ORDER BY  COUNT(e.elevator_type_id) desc ");
        return sb.toString();
    }

    /**
     * 局分局区内电梯类型分布
     *
     * @return
     */
    public String countTypeDistribTypeArea(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select COUNT(e.elevator_type_id) AS 'count',et.type_name as 'name' from elevator e ");
        sb.append(" LEFT JOIN elevator_type et ON e.elevator_type_id = et.id ");
        sb.append(" where e.branch_office in (select py.id from party py where py.parent_id=#{id} and party_type='4') and elevator_type_id is not null ");
        sb.append(" GROUP BY e.elevator_type_id ORDER BY  COUNT(e.elevator_type_id) desc ");
        return sb.toString();
    }

    /**
     *单位排行-->市数据
     */
    public String maintenanceRanksCity(Map map) {
        StringBuffer sb = new StringBuffer();
//        String partyId = map.get("partyId").toString();
        int i = (int) map.get("type");
        String param = map.get("param").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, partyName ,mark from ( ");
        sb.append(" select  ");
        sb.append(" party_name as 'partyName',  ");
        sb.append(" mark as 'mark'  ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" left join party py ON py.id = pr.party_id  ");
        sb.append(" where py.party_type="+i+"  ");
        sb.append(" and er.type='party'  order by  er.mark desc  ");
        sb.append(" )party,(SELECT @rowNum:=0) b   limit 10  ");
        return sb.toString();
    }

    /**
     *用户排行-->市数据
     */
    public String maintenanceUserRanksCity(Map map) {
        StringBuffer sb = new StringBuffer();
//        String partyId = map.get("partyId").toString();
        int i = (int) map.get("type");
        String param = map.get("param").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, userName ,IFNULL(mark,0) as mark ,partyName, ");
        sb.append(" case when IFNULL(mark,0) <200 then '初出茅庐' ");
        sb.append(" when IFNULL(mark,0) >=200 and IFNULL(mark,0) <500 then '小有成就'  ");
        sb.append(" when IFNULL(mark,0) >=500 and IFNULL(mark,0) <1000 then '电梯卫士'  ");
        sb.append(" else '电梯大咖' end as 'name' ");
        sb.append(" from (  ");
        sb.append(" select  ");
        sb.append(" py.party_name as 'partyName',  ");
        sb.append(" user_name as 'userName',  ");
        sb.append(" mark as 'mark' ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" left join party py ON py.id = pr.party_id  ");
        sb.append(" where 1=1  ");
        sb.append(" and py.party_type="+i+" ");
        sb.append(" and er.type='user'  ");
        sb.append(" order by  er.mark desc  ");
        sb.append(" ) users,(SELECT @rowNum:=0) b   limit 10 ");
        return sb.toString();
    }


    /**
     *维保单位排行-->区数据
     */
    public String maintenanceRanksBranch(Map map) {
        StringBuffer sb = new StringBuffer();
        String partyId = map.get("partyId").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, partyName ,mark from ( ");
        sb.append(" select  ");
        sb.append(" pt.party_name as 'partyName',  ");
        sb.append(" IFNULL(mark,0) as 'mark'  ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" LEFT JOIN party pt ON pt.id = pr.party_id  ");
        sb.append(" where pt.id in (select DISTINCT maintenance_party_id as id from elevator where branch_office in (select id from party where parent_id =" + partyId + " and party_type='4'))  ");
        sb.append(" and er.type='party' order by  er.mark desc  ");
        sb.append(" )party,(SELECT @rowNum:=0) b   limit 10  ");
        return sb.toString();
    }

    /**
     *使用单位排行-->区数据
     */
    public String maintenanceRanksBranchUse(Map map) {
        StringBuffer sb = new StringBuffer();
        String partyId = map.get("partyId").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, partyName ,mark from ( ");
        sb.append(" select  ");
        sb.append(" pt.party_name as 'partyName',  ");
        sb.append(" IFNULL(mark,0) as 'mark'  ");
        sb.append(" from sys_user er  ");
        sb.append(" left join party_user pr ON pr.user_id = er.id  ");
        sb.append(" LEFT JOIN party pt ON pt.id = pr.party_id  ");
        sb.append(" where pt.id in (select DISTINCT used_party_id as id from elevator where branch_office in (select id from party where parent_id =" + partyId + " and party_type='4'))  ");
        sb.append(" and er.type='party' and pt.party_type='2' order by  er.mark desc  ");
        sb.append(" )party,(SELECT @rowNum:=0) b   limit 10  ");
        return sb.toString();
    }

    /**
     *维保单位用户排行-->区数据
     */
    public String maintenanceRanksBranchUser(Map map) {
        StringBuffer sb = new StringBuffer();
        String partyId = map.get("partyId").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, userName ,IFNULL(mark,0) as mark ,partyName, ");
        sb.append(" case when IFNULL(mark,0) <200 then '初出茅庐'  ");
        sb.append(" when IFNULL(mark,0) >=200 and IFNULL(mark,0) <500 then '小有成就'  ");
        sb.append(" when IFNULL(mark,0) >=500 and IFNULL(mark,0) <1000 then '电梯卫士'  ");
        sb.append(" else '电梯大咖' end as 'name'  ");
        sb.append(" from (  ");
        sb.append(" select user_name as 'userName',mark as 'mark',party_name as 'partyName' from (  ");
        sb.append(" SELECT DISTINCT maintenance_user_first as userId FROM elevator er where er.branch_office in (select id from party where parent_id ="+partyId+" and party_type='4')  ");
        sb.append(" UNION ");
        sb.append(" select DISTINCT maintenance_user_second as userId from elevator er2 where er2.branch_office in (select id from party where parent_id ="+partyId+" and party_type='4') ");
        sb.append(" ) ur LEFT JOIN sys_user su ON su.id = ur.userId ");
        sb.append(" LEFT JOIN party_user pu ON pu.user_id = su.id ");
        sb.append(" LEFT JOIN party py ON py.id = pu.party_id ORDER BY mark desc ");
        sb.append(" ) users,(SELECT @rowNum:=0) b   limit 10 ");
        return sb.toString();
    }

    /**
     *使用单位用户排行-->区数据
     */
    public String maintenanceRanksBranchUseUser(Map map) {
        StringBuffer sb = new StringBuffer();
        String partyId = map.get("partyId").toString();

        sb.append(" select (@rowNum:=@rowNum+1) as rowNo, userName ,IFNULL(mark,0) as mark ,partyName, ");
        sb.append(" case when IFNULL(mark,0) <200 then '初出茅庐'  ");
        sb.append(" when IFNULL(mark,0) >=200 and IFNULL(mark,0) <500 then '小有成就'  ");
        sb.append(" when IFNULL(mark,0) >=500 and IFNULL(mark,0) <1000 then '电梯卫士'  ");
        sb.append(" else '电梯大咖' end as 'name'  ");
        sb.append(" from (  ");
        sb.append(" SELECT DISTINCT di.check_user_id,  ");
        sb.append(" su.user_name as 'userName',  ");
        sb.append(" su.mark as 'mark', ");
        sb.append(" py.party_name as 'partyName' ");
        sb.append(" FROM daily_inspection di ");
        sb.append(" LEFT JOIN sys_user su ON su.id = di.check_user_id ");
        sb.append(" left join party_user pr ON pr.user_id = di.check_user_id ");
        sb.append(" left join party py ON py.id = pr.party_id   ");
        sb.append(" INNER JOIN (select DISTINCT id as id from elevator where branch_office in (select id from party where parent_id ="+partyId+" and party_type='4')) ele ON ele.id = di.elevator_id  ");
        sb.append(" order by  su.mark desc  ");
        sb.append(" ) users,(SELECT @rowNum:=0) b   limit 10  ");
        return sb.toString();
    }


}
